A buffer pool typically contains a number of pages either free or in use. Under certain circumstances, it may be necessary to decrease the size of a buffer pool. Known database systems require that the size of the buffer pool be capable of decreasing dynamically (that is, on the fly) without requiring a quiesce or an exclusive access to the buffer pool for decreasing the buffer pool size. One disadvantage with dynamic resizing of buffer pools is that the size can only be reduced once the pages attempting to be freed are no longer ‘in use’ (i.e. not fixed: pinned or latched).
The operation of freeing regions of the buffer pool can be inefficient, especially if there are many ‘pinned’ pages. These pinned pages are generally frequently accessed pages in the region of the buffer pool to be freed. One complication for resizing buffer pool regions is that pages are often pinned for a long duration of time; they may be fixed once, latched and unlatched many times depending on the number of rows on the page. A standard solution is for the buffer pool resizing to serialize on pages that are ‘in use’. However, pinned pages pose a problem to this solution as they could remain pinned for long periods of time.
Current systems include implementations for resizing that do not wait for the buffer pool decrease to be fully completed. For example, when an ALTER BUFFERPOOL SQL is issued to decrease the size of a buffer pool dynamically, this command can return before the memory that is being decreased is actually freed. This is typically a “last man out” solution, where the ALTER BUFFERPOOL SQL initiates a decreasing activity and is finished before the decrease is actually accomplished.
Thus, there is need for a system that can dynamically resize a buffer pool by directing a database management system to relocate buffer pages that are pinned in the buffer pool. The need for such a system has heretofore remained unsatisfied.